package pe.edu.upc.dew.egretel.dao;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import pe.edu.upc.dew.egretel.model.Reclamo;
import pe.edu.upc.dew.egretel.util.JdbcUtils;

public class ReclamoDaoJdbc implements ReclamoDao {

    public void insertReclamo(String string) {

        Connection conn = JdbcUtils.getConnection();

        try {
            Statement st = conn.createStatement();

            String query = "INSERT INTO Reclamo (IdCliente, descripcion, telefono, IdCategoria, IdEspecialista, fechaCreacion, estado)" + "VALUES (" + string + ")";

            st.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(PersonaDaoJdbc.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Reclamo detalleReclamo(String idReclamo) {

        Connection conn = JdbcUtils.getConnection();

        try {
            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery("select REC.idReclamo, REC.descripcion, REC.telefono, CAT.descripcion as idCategoria, REC.fechaCreacion, REC.solucion, REC.fechaCierre, REC.idCliente, REC.idEspecialista, EST.descripcion as idEstado from Reclamo REC INNER JOIN Categoria CAT ON REC.idCategoria = CAT.IdCategoria INNER JOIN Estado EST ON REC.idEstado = EST.IdEstado where CAST(idReclamo AS CHAR(10))='" + idReclamo + "'");
            if (rs.next()) {

                PersonaDao personadao = new PersonaDaoJdbc();

                Reclamo reclamo = new Reclamo(Integer.parseInt(rs.getString("idReclamo")),
                        rs.getString("descripcion"),
                        rs.getString("telefono"),
                        rs.getString("idCategoria"),
                        rs.getString("fechaCreacion"),
                        rs.getString("solucion"),
                        rs.getString("fechaCierre"),
                        personadao.getPersonaId(rs.getString("idCliente")),
                        personadao.getPersonaId(rs.getString("idEspecialista")),
                        rs.getString("idEstado"));

                //ticket.setComentarios(comentariodao.getComentarios(rs.getString("idTicket")));

                return reclamo;
            }

        } catch (SQLException ex) {
            Logger.getLogger(PersonaDaoJdbc.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Reclamo> getReclamosPorIdCliente(String idCliente) {

        Connection conn = JdbcUtils.getConnection();

        try {
            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery("select REC.idReclamo, REC.descripcion, REC.telefono, CAT.descripcion as idCategoria, REC.fechaCreacion, REC.solucion, REC.fechaCierre, REC.idCliente, REC.idEspecialista, EST.descripcion as idEstado from Reclamo REC INNER JOIN Categoria CAT ON REC.idCategoria = CAT.IdCategoria INNER JOIN Estado EST ON REC.idEstado = EST.IdEstado where CAST(idCliente AS CHAR(10)) = '" + idCliente + "'");

            ArrayList<Reclamo> listaReclamos = new ArrayList<Reclamo>();

            while (rs.next()) {

                PersonaDao personadao = new PersonaDaoJdbc();

                Reclamo reclamo = new Reclamo(Integer.parseInt(rs.getString("idReclamo")),
                        rs.getString("descripcion"),
                        rs.getString("telefono"),
                        rs.getString("idCategoria"),
                        rs.getString("fechaCreacion"),
                        rs.getString("solucion"),
                        rs.getString("fechaCierre"),
                        personadao.getPersonaId(rs.getString("idCliente")),
                        personadao.getPersonaId(rs.getString("idEspecialista")),
                        rs.getString("idEstado"));

                listaReclamos.add(reclamo);
            }
            return listaReclamos;

        } catch (SQLException ex) {
            Logger.getLogger(PersonaDaoJdbc.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<Reclamo> getReclamosPorIdEspecialista(String idEspecialista) {

        Connection conn = JdbcUtils.getConnection();

        try {
            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery("select REC.idReclamo, REC.descripcion, REC.telefono, CAT.descripcion as idCategoria, REC.fechaCreacion, REC.solucion, REC.fechaCierre, REC.idCliente, REC.idEspecialista, EST.descripcion as idEstado from Reclamo REC INNER JOIN Categoria CAT ON REC.idCategoria = CAT.IdCategoria INNER JOIN Estado EST ON REC.idEstado = EST.IdEstado where CAST(idEspecialista AS CHAR(10))= '" + idEspecialista + "'");

            ArrayList<Reclamo> listaReclamos = new ArrayList<Reclamo>();

            while (rs.next()) {

                PersonaDao personadao = new PersonaDaoJdbc();

                Reclamo reclamo = new Reclamo(Integer.parseInt(rs.getString("idReclamo")),
                        rs.getString("descripcion"),
                        rs.getString("telefono"),
                        rs.getString("idCategoria"),
                        rs.getString("fechaCreacion"),
                        rs.getString("solucion"),
                        rs.getString("fechaCierre"),
                        personadao.getPersonaId(rs.getString("idCliente")),
                        personadao.getPersonaId(rs.getString("idEspecialista")),
                        rs.getString("idEstado"));

                listaReclamos.add(reclamo);
            }
            return listaReclamos;

        } catch (SQLException ex) {
            Logger.getLogger(PersonaDaoJdbc.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void updateEstadoReclamo(String idReclamo, String estado, String solucion) {

        Connection conn = JdbcUtils.getConnection();

        try {
            Statement st = conn.createStatement();

            String query = "update Reclamo set idEstado = " + estado + ",solucion = " + solucion + " where CAST(idReclamo AS CHAR(10))= " + idReclamo;

            st.executeUpdate(query);

        } catch (SQLException ex) {
            Logger.getLogger(PersonaDaoJdbc.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
